﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Data;
using System.Data.OleDb;

namespace Tools
{
    class Excel
    {
        /// <summary>
        /// 从Excel读取数据，保存到DataTable
        /// </summary>
        /// <returns></returns>
        public static string ExcelToDataTable(DataTable dataTable, HttpPostedFile file, string fileFullName)
        {
            string result = string.Empty;
            string File_Name = file.FileName;
            int File_Size = file.ContentLength;

            if (File_Name.Length == 0 || File_Size <= 0)
            {
                return "你没有选择文件或文件不存在!";
            }

            string File_ExName = File_Name.Substring(File_Name.LastIndexOf(".") + 1);//扩展名
            if (File_ExName.Equals("xls") == false)
            {
                return "不是有效的EXCEL文件!";

            }
            //上传EXCEL文件到ExcelFile目录
            file.SaveAs(fileFullName);
            //获取EXCEL文件,执行导入
            string excelConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileFullName + ";Extended Properties=Excel 8.0";
            string excelCmdstr = string.Format("select * from [Sheet1$]");

            try
            {
                OleDbConnection oleDbConnection = new OleDbConnection(excelConnect);
                oleDbConnection.Open();

                OleDbCommand excelCmd = new OleDbCommand(excelCmdstr, oleDbConnection);
                OleDbDataAdapter excelDa = new OleDbDataAdapter(excelCmd);

                DataTable dt = new DataTable();
                excelDa.Fill(dt);
                oleDbConnection.Close();
            }
            catch (Exception ex)
            {
                result = ex.Message;
            }

            return result;
        }
    }
}
